package cn.kgc.layui.dao.impl;

import cn.kgc.layui.dao.ManagerDao;
import cn.kgc.layui.dao.RoleDao;
import cn.kgc.layui.entity.Authority;
import cn.kgc.layui.entity.Manager;
import cn.kgc.layui.entity.Role;
import cn.kgc.layui.util.DruidDBUtil;
import cn.kgc.layui.vo.LoginVO;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

public class ManagerDaoImpl implements ManagerDao {
    @Override
    public List<Manager> findAllByPage(int start, int size, Manager manager) {
        // 获取QueryRunner
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql="select id, user_name userName,pass_word passWord,nick_name nickName,gender,status,create_time createTime,update_time updateTime,create_by createBy  from sys_manager where 1=1";


        try {

            String sqlWhere="";
            if(null!=manager.getUserName()&&(!StringUtils.isEmpty(manager.getUserName()))){
                sqlWhere+=" and user_name like'%"+manager.getUserName()+"%'";
            }
            if(null!=manager.getNickName()&&(!StringUtils.isEmpty(manager.getNickName()))){
                sqlWhere+=" and nick_name like'%"+manager.getNickName()+"%'";
            }
            sql=sql+sqlWhere;
            sql=sql+" limit ?,?";
            return qr.query(sql, new BeanListHandler<>(Manager.class),start, size);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return null;
    }

    /**
     * 用户登录查询
     * @param loginVO
     * @return
     */
    public Manager doLogin(LoginVO loginVO){
        try {
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="select id, user_name userName,pass_word passWord,nick_name nickName,gender,status,create_time createTime,update_time updateTime,create_by createBy from sys_manager where user_name=? and pass_word=?";
            Manager manager=qr.query(sql,new BeanHandler<>(Manager.class),loginVO.getUserName(),loginVO.getPassWord());
            return manager;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(Manager manager) throws SQLException {
        Integer result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="insert into sys_manager (user_name,pass_word,nick_name,gender,status,create_time) values(?,?,?,?,?,?)";
            qr.update(sql,manager.getUserName(),manager.getPassWord(),manager.getNickName(),manager.getGender(),manager.getStatus(),manager.getCreateTime());
            BigInteger id=qr.query("SELECT @@IDENTITY id",new ScalarHandler<BigInteger>());
            return id.intValue();
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 向角色权限关联表插入数据
     */
    public void saveManagerRole(String managerId,String roleIds) {
        String [] authIdArra= roleIds.split(",");
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="insert into sys_manager_role (manager_id,role_id) values(?,?)";
            qr.update("delete from sys_manager_role where manager_id=?",managerId);
            for (String authId:authIdArra) {
                qr.update(sql,managerId,authId);
            }
        }catch (Exception e){
            e.printStackTrace();
        }


    }

    @Override
    public int update(Manager manager) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="update sys_manager set user_name=?, pass_word=?,nick_name=?,gender=?,status=? where id=?";
            result=qr.update(sql,manager.getUserName(),manager.getPassWord(),manager.getNickName(),manager.getGender(),manager.getStatus(),manager.getId());
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int delete(String id) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="delete from sys_manager where id=?";
            result=qr.update(sql,id);
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public long selectTotalCount() {
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql = "select count(*) from sys_manager";
        try {
            return qr.query(sql,new ScalarHandler<Long>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    /**
     * 根据用户id查询拥有的权限菜单
     */
    public List<Authority> getAuth(String managerId){
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql = "select authority_id authorityId,authority_name authorityName,menu_url menuUrl,parent_id parentId,is_menu isMenu,menu_icon menuIcon,order_number orderNumber,create_time createTime,update_time updateTime,authority from sys_authority where authority_id in( " +
                "	select auth_id from sys_role_auth where role_id in( " +
                "	select role_id from sys_manager_role where manager_id=? " +
                ") " +
                ")";
        try {
            return qr.query(sql,new BeanListHandler<>(Authority.class),managerId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;

    }
}
